from utils.MySQLTool import MySQLTool
from prettytable import PrettyTable as pt

# 示例用法
tool = MySQLTool()
tool.connect()

students = []  # 用于存储学生信息的列表


def get_student_all():
    query = f"SELECT * FROM students"
    result = tool.execute_query(query)
    return result


def get_student_info(student_id):
    query = f"SELECT * FROM students WHERE student_id = {student_id}"
    result = tool.execute_query(query)
    return result


def add_student(student_id, name, grade, class_name):
    query = "INSERT INTO students (student_id, name, grade, class) VALUES (%s, %s, %s, %s)"
    values = (student_id, name, grade, class_name)
    tool.execute_insert(query, values)
    print("Student added successfully!")


def my_print(title, data):
    table = pt()
    table.field_names = title
    for d in data:
        table.add_row(d)

    return table


def add():
    """添加学生信息"""
    student_id = input("请输入学号: ")
    name = input("请输入姓名: ")
    gender = input("请输入性别: ")
    class_name = input("请输入班级: ")
    add_student(student_id, name, gender, class_name)


def delete():
    """删除学生信息"""
    student_id = input("请输入要删除的学生学号: ")
    for student in students:
        if student["学号"] == student_id:
            students.remove(student)
            print("学生信息删除成功！")
            return
    print("找不到该学生的信息！")


def update():
    """修改学生信息"""
    student_id = input("请输入要修改的学生学号: ")
    for student in students:
        if student["学号"] == student_id:
            student["姓名"] = input("请输入修改后的姓名: ")
            student["性别"] = input("请输入修改后的性别: ")
            student["班级"] = input("请输入修改后的班级: ")
            print("学生信息修改成功！")
            return
    print("找不到该学生的信息！")


def select():
    # 获取学生信息
    student_list = get_student_all()
    if student_list:

        data = []
        for student in student_list:
            data.append(list(student.values()))
        title = student_list[0].keys()
        table = my_print(title, data)
        print(table)
    else:
        print("暂时没有数据！")


def selectById():
    student_id = input("请输入要查看的学生学号: ")
    info = get_student_info(student_id)
    print(info)


def main():
    menuData = [
        [1, '添加学生信息'],
        [2, '删除学生信息'],
        [3, '修改学生信息'],
        [4, '查询所有学生信息'],
        [5, '查询当个学生信息'],
        [0, '退出']
    ]
    menu = pt()
    menu.field_names = ['序号', '选   项']
    for data in menuData:
        menu.add_row(data)
    """主函数，控制程序运行流程"""
    while True:
        print("欢迎使用学生管理系统")
        print(menu)
        choice = input("请输入功能序号: ")
        if choice == "1":
            add()
        elif choice == "2":
            delete()
        elif choice == "3":
            update()
        elif choice == "4":
            select()
        elif choice == "5":
            selectById()
        elif choice == "0":
            print("感谢使用学生管理系统，再见！")
            break
        else:
            print("无效的选择，请重新输入！")


if __name__ == '__main__':
    main()
